<html>
 <head>
  <meta charset="utf-8"/>
  <meta content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" name="viewport"/>
  <title>
   记一次苦逼的SQL查询优化  | 数螺 | NAUT IDEA
  </title>
  <link href="http://cdn.bootcss.com/bootstrap/3.3.6/css/bootstrap-theme.min.css" rel="stylesheet"/>
  <link href="http://cdn.bootcss.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet"/>
  <style type="text/css">
   #xmain img {
                  max-width: 100%;
                  display: block;
                  margin-top: 10px;
                  margin-bottom: 10px;
                }

                #xmain p {
                    line-height:150%;
                    font-size: 16px;
                    margin-top: 20px;
                }

                #xmain h2 {
                    font-size: 24px;
                }

                #xmain h3 {
                    font-size: 20px;
                }

                #xmain h4 {
                    font-size: 18px;
                }


                .header {
	           background-color: #0099ff;
	           color: #ffffff;
	           margin-bottom: 20px;
	        }

	        .header p {
                  margin: 0px;
                  padding: 10px 0;
                  display: inline-block;  
                  vertical-align: middle;
                  font-size: 16px;
               }

               .header a {
                 color: white;
               }

              .header img {
                 height: 25px;
              }
  </style>
  <script src="http://cdn.bootcss.com/jquery/3.0.0/jquery.min.js">
  </script>
  <script src="http://nautstatic-10007657.file.myqcloud.com/static/css/readability.min.js" type="text/javascript">
  </script>
  <script type="text/javascript">
   $(document).ready(function() {
                 var loc = document.location;
                 var uri = {
                  spec: "http://dataunion.org/20037.html",
                  host: "http://dataunion.org",
                  prePath: "http://dataunion.org",
                  scheme: "http",
                  pathBase: "http://dataunion.org/"
                 };
    
                 var documentClone = document.cloneNode(true);
                 var article = new Readability(uri, documentClone).parse();
     
                 document.getElementById("xmain").innerHTML = article.content;
                });
  </script>
  <!-- 1466454900: Accept with keywords: (title(0.25):记,社区,优化,数盟, topn(0.366666666667):社区,联系,数盟,信息,深度学习,行业资讯,查找,行数,数据挖掘,数据分析,人工智能,数据库,职业规划,引擎,基础架构,文章,可视化,数据,函数,办法,运算符,计划,合作伙伴,优化,有时候,编程语言,秒钟,字段,行业,分区).-->
 </head>
 <body onload="">
  <div class="header">
   <div class="container">
    <div class="row">
     <div class="col-xs-6 col-sm-6 text-left">
      <a href="/databee">
       <img src="http://nautidea-10007657.cos.myqcloud.com/logo_white.png"/>
      </a>
      <a href="/databee">
       <p>
        数螺
       </p>
      </a>
     </div>
     <div class="hidden-xs col-sm-6 text-right">
      <p>
       致力于数据科学的推广和知识传播
      </p>
     </div>
    </div>
   </div>
  </div>
  <div class="container text-center">
   <h1>
    记一次苦逼的SQL查询优化
   </h1>
  </div>
  <div class="container" id="xmain">
   ﻿﻿
   <title>
    记一次苦逼的SQL查询优化 | 数盟社区
   </title>
   <!-- All in One SEO Pack 2.2.7.6.2 by Michael Torbert of Semper Fi Web Design[32,60] -->
   <!-- /all in one seo pack -->
   <!--
<div align="center">
<a href="http://strata.oreilly.com.cn/hadoop-big-data-cn?cmp=mp-data-confreg-home-stcn16_dataunion_pc" target="_blank"><img src="http://dataunion.org/wp-content/uploads/2016/05/stratabj.jpg"/ ></a>
</div>
-->
   <header id="header-web">
    <div class="header-main">
     <hgroup class="logo">
      <h1>
       <a href="http://dataunion.org/" rel="home" title="数盟社区">
        <img src="http://dataunion.org/wp-content/themes/yzipi/images/logo.png"/>
       </a>
      </h1>
     </hgroup>
     <!--logo-->
     <nav class="header-nav">
      <ul class="menu" id="menu-%e4%b8%bb%e8%8f%9c%e5%8d%95">
       <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-has-children menu-item-71" id="menu-item-71">
        <a href="http://dataunion.org/category/events" title="events">
         活动
        </a>
        <ul class="sub-menu">
         <li class="menu-item menu-item-type-post_type menu-item-object-page menu-item-22457" id="menu-item-22457">
          <a href="http://dataunion.org/2016timeline">
           2016档期
          </a>
         </li>
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-22459" id="menu-item-22459">
          <a href="http://dataunion.org/category/parterc">
           合作会议
          </a>
         </li>
        </ul>
       </li>
       <li class="menu-item menu-item-type-taxonomy menu-item-object-category current-post-ancestor current-menu-parent current-post-parent menu-item-has-children menu-item-20869" id="menu-item-20869">
        <a href="http://dataunion.org/category/tech" title="articles">
         文章
        </a>
        <ul class="sub-menu">
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-20867" id="menu-item-20867">
          <a href="http://dataunion.org/category/tech/base" title="base">
           基础架构
          </a>
         </li>
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-3302" id="menu-item-3302">
          <a href="http://dataunion.org/category/tech/ai" title="ai">
           人工智能
          </a>
         </li>
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-3303" id="menu-item-3303">
          <a href="http://dataunion.org/category/tech/analysis" title="analysis">
           数据分析
          </a>
         </li>
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-21920" id="menu-item-21920">
          <a href="http://dataunion.org/category/tech/dm">
           数据挖掘
          </a>
         </li>
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-3314" id="menu-item-3314">
          <a href="http://dataunion.org/category/tech/viz" title="viz">
           可视化
          </a>
         </li>
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-3305" id="menu-item-3305">
          <a href="http://dataunion.org/category/tech/devl" title="devl">
           编程语言
          </a>
         </li>
        </ul>
       </li>
       <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-has-children menu-item-20876" id="menu-item-20876">
        <a href="http://dataunion.org/category/industry">
         行业
        </a>
        <ul class="sub-menu">
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-16328" id="menu-item-16328">
          <a href="http://dataunion.org/category/industry/case" title="case">
           行业应用
          </a>
         </li>
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-2112" id="menu-item-2112">
          <a href="http://dataunion.org/category/industry/demo" title="demo">
           Demo展示
          </a>
         </li>
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-21562" id="menu-item-21562">
          <a href="http://dataunion.org/category/industry/news">
           行业资讯
          </a>
         </li>
        </ul>
       </li>
       <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-311" id="menu-item-311">
        <a href="http://dataunion.org/category/sources" title="sources">
         资源
        </a>
       </li>
       <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-20870" id="menu-item-20870">
        <a href="http://dataunion.org/category/books" title="book">
         图书
        </a>
       </li>
       <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-21363" id="menu-item-21363">
        <a href="http://dataunion.org/category/training">
         课程
        </a>
       </li>
       <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-has-children menu-item-21853" id="menu-item-21853">
        <a href="http://dataunion.org/category/jobs">
         职位
        </a>
        <ul class="sub-menu">
         <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-22050" id="menu-item-22050">
          <a href="http://dataunion.org/category/career">
           职业规划
          </a>
         </li>
        </ul>
       </li>
      </ul>
     </nav>
     <!--header-nav-->
    </div>
   </header>
   <!--header-web-->
   <div id="main">
    <div id="soutab">
     <form action="http://dataunion.org/" class="search" method="get">
     </form>
    </div>
    <div id="container">
     <nav id="mbx">
      当前位置：
      <a href="http://dataunion.org">
       首页
      </a>
      &gt;
      <a href="http://dataunion.org/category/tech">
       文章
      </a>
      &gt;  正文
     </nav>
     <!--mbx-->
     <article class="content">
      <header align="centre" class="contenttitle">
       <div class="mscc">
        <h1 class="mscctitle">
         <a href="http://dataunion.org/20037.html">
          记一次苦逼的SQL查询优化
         </a>
        </h1>
        <address class="msccaddress ">
         <em>
          1,534 次阅读 -
         </em>
         <a href="http://dataunion.org/category/tech" rel="category tag">
          文章
         </a>
        </address>
       </div>
      </header>
      <div class="content-text">
       <p>
        出处：
        <a href="http://www.cnblogs.com/chenlinzhi/p/4648597.html">
         云在青天水在哪的博客
        </a>
       </p>
       <p>
        最近在维护公司项目时，需要加载某页面，总共加载也就4000多条数据，竟然需要35秒钟，要是数据增长到40000条，我估计好几分钟都搞不定。卧槽，要我是用户的话估计受不了，趁闲着没事，就想把它优化一下，走你。
       </p>
       <p>
       </p>
       <p>
        先把查询贴上：
       </p>
       <p align="left">
       </p>
       <div class="cnblogs_code">
        <img src="http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif"/>
        <p>
        </p>
        <div class="cnblogs_code_hide" id="cnblogs_code_open_ef811f49-37a8-48e3-a8e4-ba59083a3e2f">
         <div class="cnblogs_code_toolbar">
          <span class="cnblogs_code_copy">
           <p title="复制代码">
            <img src="http://common.cnblogs.com/images/copycode.gif"/>
           </p>
          </span>
         </div>
         <p>
         </p>
         <!-- Crayon Syntax Highlighter v_2.7.2_beta -->
         <div class="crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes notranslate" data-settings=" minimize scroll-mouseover" id="crayon-5768536d95ac5316316922" style=" margin-top: 12px; margin-bottom: 12px; font-size: 12px !important; line-height: 15px !important;">
          <div class="crayon-toolbar" data-settings=" mouseover overlay hide delay" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
           <span class="crayon-title">
           </span>
           <div class="crayon-tools" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
            <div class="crayon-button crayon-nums-button" title="切换是否显示行编号">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-plain-button" title="纯文本显示代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-wrap-button" title="切换自动换行">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-expand-button" title="点击展开代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-copy-button" title="复制代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-popup-button" title="在新窗口中显示代码">
             <div class="crayon-button-icon">
             </div>
            </div>
           </div>
          </div>
          <div class="crayon-info" style="min-height: 16.8px !important; line-height: 16.8px !important;">
          </div>
          <div class="crayon-plain-wrap">
           <textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 12px !important; line-height: 15px !important;" wrap="soft">
            select Pub_AidBasicInformation.AidBasicInfoId,

       Pub_AidBasicInformation.UserName,

       Pub_AidBasicInformation.District,

       Pub_AidBasicInformation.Street,

       Pub_AidBasicInformation.Community,

       Pub_AidBasicInformation.DisCard,

       Pub_Application.CreateOn AS AppCreateOn,

       Pub_User.UserName as DepartmentUserName, 

       Pub_Consult1.ConsultId,

       Pub_Consult1.CaseId,

       Clinicaltb.Clinical,AidNametb.AidName,

       Pub_Application.IsUseTraining,

       Pub_Application.ApplicationId,

       tab.num

FROM   Pub_Consult1

INNER JOIN Pub_Application ON Pub_Consult1.ApplicationId = Pub_Application.ApplicationId

INNER JOIN Pub_AidBasicInformation ON Pub_Application.AidBasicInfoId = Pub_AidBasicInformation.AidBasicInfoId                                                           

INNER JOIN(select ConsultId,dbo.f_GetClinical(ConsultId) as Clinical

            from Pub_Consult1) Clinicaltb on Clinicaltb.ConsultId=Pub_Consult1.ConsultId

left join (select distinct ApplicationId, sum(TraniningNumber) as num from dbo.Review_Aid_UseTraining_Record  where  AidReferralId is null  group by  ApplicationId) tab on tab.ApplicationId=Pub_Consult1.ApplicationId

INNER JOIN(select ConsultId,dbo.f_GetAidNamebyConsult1(ConsultId) as AidName  from Pub_Consult1) AidNametb on AidNametb.ConsultId=Pub_Consult1.ConsultId                              

LEFT OUTER JOIN Pub_User ON Pub_Application.ReviewUserId = Pub_User.UserId

     WHERE Pub_Consult1.Directory = 0

     order by Pub_Application.CreateOn desc
           </textarea>
          </div>
          <div class="crayon-main" style="">
           <table class="crayon-table">
            <tbody>
             <tr class="crayon-row">
              <td class="crayon-nums " data-settings="show">
               <div class="crayon-nums-content" style="font-size: 12px !important; line-height: 15px !important;">
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-1">
                 1
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-2">
                 2
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-3">
                 3
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-4">
                 4
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-5">
                 5
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-6">
                 6
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-7">
                 7
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-8">
                 8
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-9">
                 9
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-10">
                 10
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-11">
                 11
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-12">
                 12
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-13">
                 13
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-14">
                 14
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-15">
                 15
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-16">
                 16
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-17">
                 17
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-18">
                 18
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-19">
                 19
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-20">
                 20
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-21">
                 21
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-22">
                 22
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-23">
                 23
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-24">
                 24
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-25">
                 25
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-26">
                 26
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-27">
                 27
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-28">
                 28
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-29">
                 29
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-30">
                 30
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-31">
                 31
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-32">
                 32
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-33">
                 33
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-34">
                 34
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-35">
                 35
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-36">
                 36
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-37">
                 37
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-38">
                 38
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-39">
                 39
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-40">
                 40
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-41">
                 41
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-42">
                 42
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-43">
                 43
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-44">
                 44
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-45">
                 45
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ac5316316922-46">
                 46
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ac5316316922-47">
                 47
                </div>
               </div>
              </td>
              <td class="crayon-code">
               <div class="crayon-pre" style="font-size: 12px !important; line-height: 15px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;">
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-1">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  AidBasicInfoId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-2">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-3">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  UserName
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-4">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-5">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  District
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-6">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-7">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  Street
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-8">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-9">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  Community
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-10">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-11">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  DisCard
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-12">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-13">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  CreateOn
                 </span>
                 <span class="crayon-st">
                  AS
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  AppCreateOn
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-14">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-15">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_User
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  UserName
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  DepartmentUserName
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-h">
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-16">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-17">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-18">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-19">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  CaseId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-20">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-21">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Clinicaltb
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  Clinical
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-v">
                  AidNametb
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  AidName
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-22">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-23">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  IsUseTraining
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-24">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-25">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-26">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-27">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  tab
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  num
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-28">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-29">
                 <span class="crayon-e">
                  FROM
                 </span>
                 <span class="crayon-e">
                  Pub_Consult1
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-30">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-31">
                 <span class="crayon-e">
                  INNER
                 </span>
                 <span class="crayon-e">
                  JOIN
                 </span>
                 <span class="crayon-e">
                  Pub_Application
                 </span>
                 <span class="crayon-e">
                  ON
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  ApplicationId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-32">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-33">
                 <span class="crayon-e">
                  INNER
                 </span>
                 <span class="crayon-e">
                  JOIN
                 </span>
                 <span class="crayon-e">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-e">
                  ON
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  AidBasicInfoId
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  AidBasicInfoId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-34">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-35">
                 <span class="crayon-e">
                  INNER
                 </span>
                 <span class="crayon-e">
                  JOIN
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  f_GetClinical
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  Clinical
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-36">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-37">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  from
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  Clinicaltb
                 </span>
                 <span class="crayon-e">
                  on
                 </span>
                 <span class="crayon-v">
                  Clinicaltb
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  ConsultId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-38">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-39">
                 <span class="crayon-e">
                  left
                 </span>
                 <span class="crayon-e">
                  join
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-e">
                  distinct
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  sum
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-v">
                  TraniningNumber
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  num
                 </span>
                 <span class="crayon-e">
                  from
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  Review_Aid_UseTraining_Record
                 </span>
                 <span class="crayon-e">
                  where
                 </span>
                 <span class="crayon-e">
                  AidReferralId
                 </span>
                 <span class="crayon-st">
                  is
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-t">
                  null
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  group
                 </span>
                 <span class="crayon-e">
                  by
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  tab
                 </span>
                 <span class="crayon-e">
                  on
                 </span>
                 <span class="crayon-v">
                  tab
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  ApplicationId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-40">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-41">
                 <span class="crayon-e">
                  INNER
                 </span>
                 <span class="crayon-e">
                  JOIN
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  f_GetAidNamebyConsult1
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  AidName
                 </span>
                 <span class="crayon-e">
                  from
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  AidNametb
                 </span>
                 <span class="crayon-e">
                  on
                 </span>
                 <span class="crayon-v">
                  AidNametb
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  ConsultId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-42">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-43">
                 <span class="crayon-e">
                  LEFT
                 </span>
                 <span class="crayon-e">
                  OUTER
                 </span>
                 <span class="crayon-e">
                  JOIN
                 </span>
                 <span class="crayon-e">
                  Pub_User
                 </span>
                 <span class="crayon-e">
                  ON
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ReviewUserId
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_User
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  UserId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-44">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-45">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  WHERE
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  Directory
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-cn">
                  0
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ac5316316922-46">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ac5316316922-47">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  order
                 </span>
                 <span class="crayon-e">
                  by
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  CreateOn
                 </span>
                 <span class="crayon-v">
                  desc
                 </span>
                </div>
               </div>
              </td>
             </tr>
            </tbody>
           </table>
          </div>
         </div>
         <!-- [Format Time: 0.0500 seconds] -->
         <p>
         </p>
         <div class="cnblogs_code_toolbar">
          <span class="cnblogs_code_copy">
           <p title="复制代码">
            <img src="http://common.cnblogs.com/images/copycode.gif"/>
           </p>
          </span>
         </div>
        </div>
       </div>
       <p>
       </p>
       <p align="left">
        执行后有图有真相：
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151547250016105.png"/>
       </p>
       <p align="left">
       </p>
       <p align="left">
        这么慢，没办法就去看看查询计划是怎么样：
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151547495168414.jpg"/>
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151548120016013.jpg"/>
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151548219543305.jpg"/>
       </p>
       <p align="left">
        这是该sql查询里面执行三个函数时生成查询计划的截图，一看就知道，执行时开销比较大，而且都是花费在聚集索引扫描上，把鼠标放到聚集索引扫描的方块上面，依次看到如下详细计划：
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151548412827233.png"/>
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151550175322162.jpg"/>
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151550295486325.png"/>
       </p>
       <p align="left">
       </p>
       <p align="left">
        从这几张图里，可以看到查询I/O开销，运算符开销，估计行数，以及操作的对象和查询条件，这些都为优化查询提供了有利证据。第1，3张图IO开销比较大，第2张图估计行数比较大，再根据其它信息，首先想到的应该是去建立索引，不行的话再去改查询。
       </p>
       <p align="left">
        先看看数据库引擎优化顾问能给我们提供什么优化信息，有时候它能够帮我们提供有效的信息，比如创建统计，索引，分区什么的。
       </p>
       <p align="left">
        先打开SQL Server Profiler 把刚刚执行的查询另存为跟踪(.trc)文件,再打开数据库引擎优化顾问，做如下图操作
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151550488918780.jpg"/>
       </p>
       <p align="left">
        最后生成的建议报告如下：
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151551011267644.jpg"/>
       </p>
       <p align="left">
       </p>
       <p align="left">
        在这里可以单击查看一些建议，分区，创建索引，根据提示创建了如下索引：
       </p>
       <div class="cnblogs_code">
        <img src="http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif"/>
        <p>
        </p>
        <div class="cnblogs_code_hide" id="cnblogs_code_open_65b131cf-d523-4f38-b5ce-9059d7517ee6">
         <div class="cnblogs_code_toolbar">
          <span class="cnblogs_code_copy">
           <p title="复制代码">
            <img src="http://common.cnblogs.com/images/copycode.gif"/>
           </p>
          </span>
         </div>
         <p>
         </p>
         <!-- Crayon Syntax Highlighter v_2.7.2_beta -->
         <div class="crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes notranslate" data-settings=" minimize scroll-mouseover" id="crayon-5768536d95ad6808668270" style=" margin-top: 12px; margin-bottom: 12px; font-size: 12px !important; line-height: 15px !important;">
          <div class="crayon-toolbar" data-settings=" mouseover overlay hide delay" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
           <span class="crayon-title">
           </span>
           <div class="crayon-tools" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
            <div class="crayon-button crayon-nums-button" title="切换是否显示行编号">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-plain-button" title="纯文本显示代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-wrap-button" title="切换自动换行">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-expand-button" title="点击展开代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-copy-button" title="复制代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-popup-button" title="在新窗口中显示代码">
             <div class="crayon-button-icon">
             </div>
            </div>
           </div>
          </div>
          <div class="crayon-info" style="min-height: 16.8px !important; line-height: 16.8px !important;">
          </div>
          <div class="crayon-plain-wrap">
           <textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 12px !important; line-height: 15px !important;" wrap="soft">
            CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_AidBasicInformation]

(

    [AidBasicInfoId] ASC

)


CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_Application]

(

    [ApplicationId] ASC,[ReviewUserId] ASC,[AidBasicInfoId] ASC,[CreateOn] ASC

)

CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_Consult1]

(

    [Directory] ASC,[ApplicationId] ASC

)

 

CREATE NONCLUSTERED INDEX idnex1 ON [dbo].[Review_Aid_UseTraining_Record]

(

    [AidReferralId] ASC,[ApplicationId] ASC

)
           </textarea>
          </div>
          <div class="crayon-main" style="">
           <table class="crayon-table">
            <tbody>
             <tr class="crayon-row">
              <td class="crayon-nums " data-settings="show">
               <div class="crayon-nums-content" style="font-size: 12px !important; line-height: 15px !important;">
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-1">
                 1
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-2">
                 2
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-3">
                 3
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-4">
                 4
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-5">
                 5
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-6">
                 6
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-7">
                 7
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-8">
                 8
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-9">
                 9
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-10">
                 10
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-11">
                 11
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-12">
                 12
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-13">
                 13
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-14">
                 14
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-15">
                 15
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-16">
                 16
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-17">
                 17
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-18">
                 18
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-19">
                 19
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-20">
                 20
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-21">
                 21
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-22">
                 22
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-23">
                 23
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-24">
                 24
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-25">
                 25
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-26">
                 26
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-27">
                 27
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-28">
                 28
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-29">
                 29
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-30">
                 30
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-31">
                 31
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-32">
                 32
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ad6808668270-33">
                 33
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ad6808668270-34">
                 34
                </div>
               </div>
              </td>
              <td class="crayon-code">
               <div class="crayon-pre" style="font-size: 12px !important; line-height: 15px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;">
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-1">
                 <span class="crayon-e">
                  CREATE
                 </span>
                 <span class="crayon-e">
                  NONCLUSTERED
                 </span>
                 <span class="crayon-e">
                  INDEX
                 </span>
                 <span class="crayon-e">
                  index1
                 </span>
                 <span class="crayon-i">
                  ON
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-2">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-3">
                 <span class="crayon-sy">
                  (
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-4">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-5">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  AidBasicInfoId
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-i">
                  ASC
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-6">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-7">
                 <span class="crayon-sy">
                  )
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-8">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-9">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-10">
                 <span class="crayon-e">
                  CREATE
                 </span>
                 <span class="crayon-e">
                  NONCLUSTERED
                 </span>
                 <span class="crayon-e">
                  INDEX
                 </span>
                 <span class="crayon-e">
                  index1
                 </span>
                 <span class="crayon-i">
                  ON
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-11">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-12">
                 <span class="crayon-sy">
                  (
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-13">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-14">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  ASC
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  ReviewUserId
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  ASC
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  AidBasicInfoId
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  ASC
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  CreateOn
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-i">
                  ASC
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-15">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-16">
                 <span class="crayon-sy">
                  )
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-17">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-18">
                 <span class="crayon-e">
                  CREATE
                 </span>
                 <span class="crayon-e">
                  NONCLUSTERED
                 </span>
                 <span class="crayon-e">
                  INDEX
                 </span>
                 <span class="crayon-e">
                  index1
                 </span>
                 <span class="crayon-i">
                  ON
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-19">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-20">
                 <span class="crayon-sy">
                  (
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-21">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-22">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  Directory
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  ASC
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-i">
                  ASC
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-23">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-24">
                 <span class="crayon-sy">
                  )
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-25">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-26">
                 <span class="crayon-h">
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-27">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-28">
                 <span class="crayon-e">
                  CREATE
                 </span>
                 <span class="crayon-e">
                  NONCLUSTERED
                 </span>
                 <span class="crayon-e">
                  INDEX
                 </span>
                 <span class="crayon-e">
                  idnex1
                 </span>
                 <span class="crayon-i">
                  ON
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  Review_Aid_UseTraining_Record
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-29">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-30">
                 <span class="crayon-sy">
                  (
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-31">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-32">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  AidReferralId
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  ASC
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-sy">
                  [
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-sy">
                  ]
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-i">
                  ASC
                 </span>
                </div>
                <div class="crayon-line" id="crayon-5768536d95ad6808668270-33">
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ad6808668270-34">
                 <span class="crayon-sy">
                  )
                 </span>
                </div>
               </div>
              </td>
             </tr>
            </tbody>
           </table>
          </div>
         </div>
         <!-- [Format Time: 0.0141 seconds] -->
         <p>
         </p>
         <div class="cnblogs_code_toolbar">
          <span class="cnblogs_code_copy">
           <p title="复制代码">
            <img src="http://common.cnblogs.com/images/copycode.gif"/>
           </p>
          </span>
         </div>
        </div>
       </div>
       <p>
       </p>
       <p align="left">
        索引创建后，再次执行查询，原以为可提高效率，没想到我勒个去，还是要30几秒，几乎没什么改善，优化引擎顾问有时候也会失灵，在这里只是给大家演示有这种解决方案去解决问题，有时候还是靠谱的，只是这次不靠谱。没办法，只有打开函数仔细瞅瞅，再结合上面的查询计划详细图，删除先前创建的索引，然后创建了如下索引：
       </p>
       <div class="cnblogs_code">
        <img src="http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif"/>
        <p>
        </p>
        <div class="cnblogs_code_hide" id="cnblogs_code_open_6c87e1ad-fff0-4c73-88ab-f8c6c8b7944a">
         <div class="cnblogs_code_toolbar">
          <span class="cnblogs_code_copy">
           <p title="复制代码">
            <img src="http://common.cnblogs.com/images/copycode.gif"/>
           </p>
          </span>
         </div>
         <p>
         </p>
         <!-- Crayon Syntax Highlighter v_2.7.2_beta -->
         <div class="crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes notranslate" data-settings=" minimize scroll-mouseover" id="crayon-5768536d95ae1738331889" style=" margin-top: 12px; margin-bottom: 12px; font-size: 12px !important; line-height: 15px !important;">
          <div class="crayon-toolbar" data-settings=" mouseover overlay hide delay" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
           <span class="crayon-title">
           </span>
           <div class="crayon-tools" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
            <div class="crayon-button crayon-nums-button" title="切换是否显示行编号">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-plain-button" title="纯文本显示代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-wrap-button" title="切换自动换行">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-expand-button" title="点击展开代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-copy-button" title="复制代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-popup-button" title="在新窗口中显示代码">
             <div class="crayon-button-icon">
             </div>
            </div>
           </div>
          </div>
          <div class="crayon-info" style="min-height: 16.8px !important; line-height: 16.8px !important;">
          </div>
          <div class="crayon-plain-wrap">
           <textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 12px !important; line-height: 15px !important;" wrap="soft">
            CREATE NONCLUSTERED INDEX index1 ON dbo.Report_AdapterAssessment_Aid

(

    AdapterAssessmentId ASC, ProductDirAId  ASC

)

CREATE NONCLUSTERED INDEX index1 ON dbo.Report_AdapterAssessment

(

    ConsultId ASC

)
           </textarea>
          </div>
          <div class="crayon-main" style="">
           <table class="crayon-table">
            <tbody>
             <tr class="crayon-row">
              <td class="crayon-nums " data-settings="show">
               <div class="crayon-nums-content" style="font-size: 12px !important; line-height: 15px !important;">
                <div class="crayon-num" data-line="crayon-5768536d95ae1738331889-1">
                 1
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ae1738331889-2">
                 2
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ae1738331889-3">
                 3
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ae1738331889-4">
                 4
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ae1738331889-5">
                 5
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ae1738331889-6">
                 6
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ae1738331889-7">
                 7
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ae1738331889-8">
                 8
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ae1738331889-9">
                 9
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ae1738331889-10">
                 10
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ae1738331889-11">
                 11
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ae1738331889-12">
                 12
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ae1738331889-13">
                 13
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ae1738331889-14">
                 14
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95ae1738331889-15">
                 15
                </div>
               </div>
              </td>
              <td class="crayon-code">
               <div class="crayon-pre" style="font-size: 12px !important; line-height: 15px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;">
                <div class="crayon-line" id="crayon-5768536d95ae1738331889-1">
                 <span class="crayon-e">
                  CREATE
                 </span>
                 <span class="crayon-e">
                  NONCLUSTERED
                 </span>
                 <span class="crayon-e">
                  INDEX
                 </span>
                 <span class="crayon-e">
                  index1
                 </span>
                 <span class="crayon-e">
                  ON
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  Report_AdapterAssessment_Aid
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ae1738331889-2">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ae1738331889-3">
                 <span class="crayon-sy">
                  (
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ae1738331889-4">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ae1738331889-5">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  AdapterAssessmentId
                 </span>
                 <span class="crayon-v">
                  ASC
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  ProductDirAId
                 </span>
                 <span class="crayon-i">
                  ASC
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ae1738331889-6">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ae1738331889-7">
                 <span class="crayon-sy">
                  )
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ae1738331889-8">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ae1738331889-9">
                 <span class="crayon-e">
                  CREATE
                 </span>
                 <span class="crayon-e">
                  NONCLUSTERED
                 </span>
                 <span class="crayon-e">
                  INDEX
                 </span>
                 <span class="crayon-e">
                  index1
                 </span>
                 <span class="crayon-e">
                  ON
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  Report_AdapterAssessment
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ae1738331889-10">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ae1738331889-11">
                 <span class="crayon-sy">
                  (
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ae1738331889-12">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ae1738331889-13">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  ConsultId
                 </span>
                 <span class="crayon-i">
                  ASC
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ae1738331889-14">
                </div>
                <div class="crayon-line" id="crayon-5768536d95ae1738331889-15">
                 <span class="crayon-sy">
                  )
                 </span>
                </div>
               </div>
              </td>
             </tr>
            </tbody>
           </table>
          </div>
         </div>
         <!-- [Format Time: 0.0079 seconds] -->
         <p>
         </p>
         <div class="cnblogs_code_toolbar">
          <span class="cnblogs_code_copy">
           <p title="复制代码">
            <img src="http://common.cnblogs.com/images/copycode.gif"/>
           </p>
          </span>
         </div>
        </div>
       </div>
       <p>
       </p>
       <p align="left">
        再次执行查询
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151551292356322.jpg"/>
       </p>
       <p align="left">
        好了，只需3.5秒，差不多提高10倍速度，看来这次是凑效了哈。
       </p>
       <p align="left">
       </p>
       <p align="left">
        再来看看查询计划是否有改变，上张图来说明下问题：
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151551436416854.jpg"/>
       </p>
       <p align="left">
        从上图当中我们可以看到，索引扫描不见了，只有索引查找，聚集索引查找，键查找，而且运算符开销，I/O开销都降低了很多。索引扫描(Index Scan),聚集索引扫描(Clustered Index Scan)跟表扫描(Table Scan)差不多，基本上是逐行去扫描表记录，速度很慢，而索引查找(Index Seek)，聚集索引查找，键查找都相当的快。优化查询的目的就是尽量把那些带有XXXX扫描的去掉，换成XXXX查找。
       </p>
       <p align="left">
        这样够了吗？但是回头又想想，4000多条数据得3.5秒钟，还是有点慢了，应该还能再快点，所以决定再去修改查询。看看查询，能优化的也只有那个三个函数了。
       </p>
       <p align="left">
        为了看函数执行效果先删除索引，看看查询中函数f_GetAidNamebyConsult1要干的事情，截取查询中与该函数有关的子查询：
       </p>
       <div class="cnblogs_code">
        <!-- Crayon Syntax Highlighter v_2.7.2_beta -->
        <div class="crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes notranslate" data-settings=" minimize scroll-mouseover" id="crayon-5768536d95ae9454248808" style=" margin-top: 12px; margin-bottom: 12px; font-size: 12px !important; line-height: 15px !important;">
         <div class="crayon-toolbar" data-settings=" mouseover overlay hide delay" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
          <span class="crayon-title">
          </span>
          <div class="crayon-tools" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
           <div class="crayon-button crayon-nums-button" title="切换是否显示行编号">
            <div class="crayon-button-icon">
            </div>
           </div>
           <div class="crayon-button crayon-plain-button" title="纯文本显示代码">
            <div class="crayon-button-icon">
            </div>
           </div>
           <div class="crayon-button crayon-wrap-button" title="切换自动换行">
            <div class="crayon-button-icon">
            </div>
           </div>
           <div class="crayon-button crayon-expand-button" title="点击展开代码">
            <div class="crayon-button-icon">
            </div>
           </div>
           <div class="crayon-button crayon-copy-button" title="复制代码">
            <div class="crayon-button-icon">
            </div>
           </div>
           <div class="crayon-button crayon-popup-button" title="在新窗口中显示代码">
            <div class="crayon-button-icon">
            </div>
           </div>
          </div>
         </div>
         <div class="crayon-info" style="min-height: 16.8px !important; line-height: 16.8px !important;">
         </div>
         <div class="crayon-plain-wrap">
          <textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 12px !important; line-height: 15px !important;" wrap="soft">
           select Pub_Consult1.ConsultId,AidName from (select ConsultId,dbo.f_GetAidNamebyConsult1(ConsultId) as AidName

from Pub_Consult1) AidNametb inner join Pub_Consult1

on AidNametb.ConsultId=Pub_Consult1.ConsultId
          </textarea>
         </div>
         <div class="crayon-main" style="">
          <table class="crayon-table">
           <tbody>
            <tr class="crayon-row">
             <td class="crayon-nums " data-settings="show">
              <div class="crayon-nums-content" style="font-size: 12px !important; line-height: 15px !important;">
               <div class="crayon-num" data-line="crayon-5768536d95ae9454248808-1">
                1
               </div>
               <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ae9454248808-2">
                2
               </div>
               <div class="crayon-num" data-line="crayon-5768536d95ae9454248808-3">
                3
               </div>
               <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95ae9454248808-4">
                4
               </div>
               <div class="crayon-num" data-line="crayon-5768536d95ae9454248808-5">
                5
               </div>
              </div>
             </td>
             <td class="crayon-code">
              <div class="crayon-pre" style="font-size: 12px !important; line-height: 15px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;">
               <div class="crayon-line" id="crayon-5768536d95ae9454248808-1">
                <span class="crayon-e">
                 select
                </span>
                <span class="crayon-v">
                 Pub_Consult1
                </span>
                <span class="crayon-sy">
                 .
                </span>
                <span class="crayon-v">
                 ConsultId
                </span>
                <span class="crayon-sy">
                 ,
                </span>
                <span class="crayon-e">
                 AidName
                </span>
                <span class="crayon-e">
                 from
                </span>
                <span class="crayon-h">
                </span>
                <span class="crayon-sy">
                 (
                </span>
                <span class="crayon-e">
                 select
                </span>
                <span class="crayon-v">
                 ConsultId
                </span>
                <span class="crayon-sy">
                 ,
                </span>
                <span class="crayon-v">
                 dbo
                </span>
                <span class="crayon-sy">
                 .
                </span>
                <span class="crayon-e">
                 f_GetAidNamebyConsult1
                </span>
                <span class="crayon-sy">
                 (
                </span>
                <span class="crayon-v">
                 ConsultId
                </span>
                <span class="crayon-sy">
                 )
                </span>
                <span class="crayon-h">
                </span>
                <span class="crayon-st">
                 as
                </span>
                <span class="crayon-h">
                </span>
                <span class="crayon-e">
                 AidName
                </span>
               </div>
               <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ae9454248808-2">
               </div>
               <div class="crayon-line" id="crayon-5768536d95ae9454248808-3">
                <span class="crayon-e">
                 from
                </span>
                <span class="crayon-v">
                 Pub_Consult1
                </span>
                <span class="crayon-sy">
                 )
                </span>
                <span class="crayon-h">
                </span>
                <span class="crayon-e">
                 AidNametb
                </span>
                <span class="crayon-e">
                 inner
                </span>
                <span class="crayon-e">
                 join
                </span>
                <span class="crayon-e">
                 Pub_Consult1
                </span>
               </div>
               <div class="crayon-line crayon-striped-line" id="crayon-5768536d95ae9454248808-4">
               </div>
               <div class="crayon-line" id="crayon-5768536d95ae9454248808-5">
                <span class="crayon-e">
                 on
                </span>
                <span class="crayon-v">
                 AidNametb
                </span>
                <span class="crayon-sy">
                 .
                </span>
                <span class="crayon-v">
                 ConsultId
                </span>
                <span class="crayon-o">
                 =
                </span>
                <span class="crayon-v">
                 Pub_Consult1
                </span>
                <span class="crayon-sy">
                 .
                </span>
                <span class="crayon-v">
                 ConsultId
                </span>
               </div>
              </div>
             </td>
            </tr>
           </tbody>
          </table>
         </div>
        </div>
        <!-- [Format Time: 0.0018 seconds] -->
       </div>
       <p>
       </p>
       <p align="left">
        得到下图的结果：
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151552066266367.jpg"/>
       </p>
       <p align="left">
       </p>
       <p align="left">
        没想到就这么点数据竟然要46秒，看来这个函数真的是罪魁祸首。
       </p>
       <p align="left">
       </p>
       <p align="left">
        该函数的具体代码就不贴出来了，而且该函数里面还欠套的另外一个函数，本身函数执行起来就慢，更何况还函数里子查询还包含函数。其实根据几相关联的表去查询几个字段，并且把一个字段的值合并到同一行，这样没必要用函数或存储过程，用子查询再加sql for xml path就行了，把该函数改成如下查询：
       </p>
       <div class="cnblogs_code">
        <img src="http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif"/>
        <p>
        </p>
        <div class="cnblogs_code_hide" id="cnblogs_code_open_f64fff4a-3ddd-4fc6-8a40-ea2fa9e121fe">
         <div class="cnblogs_code_toolbar">
          <span class="cnblogs_code_copy">
           <p title="复制代码">
            <img src="http://common.cnblogs.com/images/copycode.gif"/>
           </p>
          </span>
         </div>
         <p>
         </p>
         <!-- Crayon Syntax Highlighter v_2.7.2_beta -->
         <div class="crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes notranslate" data-settings=" minimize scroll-mouseover" id="crayon-5768536d95af0089837780" style=" margin-top: 12px; margin-bottom: 12px; font-size: 12px !important; line-height: 15px !important;">
          <div class="crayon-toolbar" data-settings=" mouseover overlay hide delay" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
           <span class="crayon-title">
           </span>
           <div class="crayon-tools" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
            <div class="crayon-button crayon-nums-button" title="切换是否显示行编号">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-plain-button" title="纯文本显示代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-wrap-button" title="切换自动换行">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-expand-button" title="点击展开代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-copy-button" title="复制代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-popup-button" title="在新窗口中显示代码">
             <div class="crayon-button-icon">
             </div>
            </div>
           </div>
          </div>
          <div class="crayon-info" style="min-height: 16.8px !important; line-height: 16.8px !important;">
          </div>
          <div class="crayon-plain-wrap">
           <textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 12px !important; line-height: 15px !important;" wrap="soft">
            with cte1 as

(

    select A.AdapterAssessmentId,case when B.AidName is null then A .AidName else B.AidName end AidName

    from Report_AdapterAssessment_Aid as A left join Pub_ProductDir as B

    on A.ProductDirAId=B.ProductDirAId

),

 cte2 as

(

    --根据AdapterAssessmentId分组并合并AidName字段值

    select AdapterAssessmentId,(select AidName+',' from cte1

                              where AdapterAssessmentId= tb.AdapterAssessmentId

                              for xml path(''))as AidName

    from cte1 as tb

    group by AdapterAssessmentId

),

cte3 as

(

    select ConsultId,LEFT(AidName,LEN(AidName)-1) as AidName

    from

    (

       select Pub_Consult1.ConsultId,cte2.AidName from Pub_Consult1,Report_AdapterAssessment,cte2

       where Pub_Consult1.ConsultId=Report_AdapterAssessment.ConsultId

       and Report_AdapterAssessment.AdapterAssessmentId=cte2.AdapterAssessmentId

       and  Report_AdapterAssessment.AssessTuiJian is null

    ) as tb)
           </textarea>
          </div>
          <div class="crayon-main" style="">
           <table class="crayon-table">
            <tbody>
             <tr class="crayon-row">
              <td class="crayon-nums " data-settings="show">
               <div class="crayon-nums-content" style="font-size: 12px !important; line-height: 15px !important;">
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-1">
                 1
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-2">
                 2
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-3">
                 3
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-4">
                 4
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-5">
                 5
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-6">
                 6
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-7">
                 7
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-8">
                 8
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-9">
                 9
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-10">
                 10
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-11">
                 11
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-12">
                 12
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-13">
                 13
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-14">
                 14
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-15">
                 15
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-16">
                 16
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-17">
                 17
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-18">
                 18
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-19">
                 19
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-20">
                 20
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-21">
                 21
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-22">
                 22
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-23">
                 23
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-24">
                 24
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-25">
                 25
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-26">
                 26
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-27">
                 27
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-28">
                 28
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-29">
                 29
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-30">
                 30
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-31">
                 31
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-32">
                 32
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-33">
                 33
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-34">
                 34
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-35">
                 35
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-36">
                 36
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-37">
                 37
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-38">
                 38
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-39">
                 39
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-40">
                 40
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-41">
                 41
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-42">
                 42
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-43">
                 43
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-44">
                 44
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-45">
                 45
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-46">
                 46
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-47">
                 47
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af0089837780-48">
                 48
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af0089837780-49">
                 49
                </div>
               </div>
              </td>
              <td class="crayon-code">
               <div class="crayon-pre" style="font-size: 12px !important; line-height: 15px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;">
                <div class="crayon-line" id="crayon-5768536d95af0089837780-1">
                 <span class="crayon-e">
                  with
                 </span>
                 <span class="crayon-e">
                  cte1
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-2">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-3">
                 <span class="crayon-sy">
                  (
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-4">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-5">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-i">
                  select
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  A
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  AdapterAssessmentId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-st">
                  case
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-i">
                  when
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  B
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  AidName
                 </span>
                 <span class="crayon-st">
                  is
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-t">
                  null
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-st">
                  then
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-i">
                  A
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  AidName
                 </span>
                 <span class="crayon-st">
                  else
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  B
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  AidName
                 </span>
                 <span class="crayon-st">
                  end
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  AidName
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-6">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-7">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  from
                 </span>
                 <span class="crayon-e">
                  Report_AdapterAssessment_Aid
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-i">
                  A
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  left
                 </span>
                 <span class="crayon-e">
                  join
                 </span>
                 <span class="crayon-e">
                  Pub_ProductDir
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-i">
                  B
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-8">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-9">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-i">
                  on
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  A
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ProductDirAId
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-v">
                  B
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-i">
                  ProductDirAId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-10">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-11">
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-12">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-13">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  cte2
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-14">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-15">
                 <span class="crayon-sy">
                  (
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-16">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-17">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-o">
                  --
                 </span>
                 根据
                 <span class="crayon-i">
                  AdapterAssessmentId
                 </span>
                 分组并合并
                 <span class="crayon-i">
                  AidName
                 </span>
                 字段值
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-18">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-19">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-v">
                  AdapterAssessmentId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-v">
                  AidName
                 </span>
                 <span class="crayon-o">
                  +
                 </span>
                 <span class="crayon-s">
                  ','
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  from
                 </span>
                 <span class="crayon-e">
                  cte1
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-20">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-21">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  where
                 </span>
                 <span class="crayon-v">
                  AdapterAssessmentId
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  tb
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  AdapterAssessmentId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-22">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-23">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-st">
                  for
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  xml
                 </span>
                 <span class="crayon-e">
                  path
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-s">
                  ''
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  AidName
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-24">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-25">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  from
                 </span>
                 <span class="crayon-e">
                  cte1
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  tb
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-26">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-27">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  group
                 </span>
                 <span class="crayon-e">
                  by
                 </span>
                 <span class="crayon-i">
                  AdapterAssessmentId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-28">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-29">
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-30">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-31">
                 <span class="crayon-e">
                  cte3
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-32">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-33">
                 <span class="crayon-sy">
                  (
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-34">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-35">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-e">
                  LEFT
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-v">
                  AidName
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-e">
                  LEN
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-v">
                  AidName
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-o">
                  -
                 </span>
                 <span class="crayon-cn">
                  1
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  AidName
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-36">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-37">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  from
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-38">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-39">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-40">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-41">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-v">
                  cte2
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  AidName
                 </span>
                 <span class="crayon-e">
                  from
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-v">
                  Report_AdapterAssessment
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-e">
                  cte2
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-42">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-43">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  where
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-v">
                  Report_AdapterAssessment
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  ConsultId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-44">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-45">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-st">
                  and
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Report_AdapterAssessment
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  AdapterAssessmentId
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-v">
                  cte2
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  AdapterAssessmentId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-46">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-47">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-st">
                  and
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Report_AdapterAssessment
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  AssessTuiJian
                 </span>
                 <span class="crayon-st">
                  is
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-t">
                  null
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af0089837780-48">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af0089837780-49">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  tb
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                </div>
               </div>
              </td>
             </tr>
            </tbody>
           </table>
          </div>
         </div>
         <!-- [Format Time: 0.0135 seconds] -->
         <p>
         </p>
         <div class="cnblogs_code_toolbar">
          <span class="cnblogs_code_copy">
           <p title="复制代码">
            <img src="http://common.cnblogs.com/images/copycode.gif"/>
           </p>
          </span>
         </div>
        </div>
       </div>
       <p>
       </p>
       <p align="left">
        这样查询出来的结果在没有索引的情况下不到1秒钟就行了。再把主查询写了：
       </p>
       <div class="cnblogs_code">
        <img src="http://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif"/>
        <p>
        </p>
        <div class="cnblogs_code_hide" id="cnblogs_code_open_0f39c01b-0f99-4217-b8c2-ec64e7f8504d">
         <div class="cnblogs_code_toolbar">
          <span class="cnblogs_code_copy">
           <p title="复制代码">
            <img src="http://common.cnblogs.com/images/copycode.gif"/>
           </p>
          </span>
         </div>
         <p>
         </p>
         <!-- Crayon Syntax Highlighter v_2.7.2_beta -->
         <div class="crayon-syntax crayon-theme-classic crayon-font-monaco crayon-os-pc print-yes notranslate" data-settings=" minimize scroll-mouseover" id="crayon-5768536d95af8723694143" style=" margin-top: 12px; margin-bottom: 12px; font-size: 12px !important; line-height: 15px !important;">
          <div class="crayon-toolbar" data-settings=" mouseover overlay hide delay" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
           <span class="crayon-title">
           </span>
           <div class="crayon-tools" style="font-size: 12px !important;height: 18px !important; line-height: 18px !important;">
            <div class="crayon-button crayon-nums-button" title="切换是否显示行编号">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-plain-button" title="纯文本显示代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-wrap-button" title="切换自动换行">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-expand-button" title="点击展开代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-copy-button" title="复制代码">
             <div class="crayon-button-icon">
             </div>
            </div>
            <div class="crayon-button crayon-popup-button" title="在新窗口中显示代码">
             <div class="crayon-button-icon">
             </div>
            </div>
           </div>
          </div>
          <div class="crayon-info" style="min-height: 16.8px !important; line-height: 16.8px !important;">
          </div>
          <div class="crayon-plain-wrap">
           <textarea class="crayon-plain print-no" data-settings="dblclick" readonly="" style="-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 12px !important; line-height: 15px !important;" wrap="soft">
            select distinct  Pub_AidBasicInformation.AidBasicInfoId,

       Pub_AidBasicInformation.UserName,

       Pub_AidBasicInformation.District,

       Pub_AidBasicInformation.Street,

       Pub_AidBasicInformation.Community,

       Pub_AidBasicInformation.DisCard,

       Pub_Application.CreateOn AS AppCreateOn,

       Pub_User.UserName as DepartmentUserName, 

       Pub_Consult1.ConsultId,

       Pub_Consult1.CaseId,

       Clinicaltb.Clinical,

       cte3.AidName,

       Pub_Application.IsUseTraining,

       Pub_Application.ApplicationId,

       tab.num

from   Pub_Consult1

INNER JOIN Pub_Application ON Pub_Consult1.ApplicationId = Pub_Application.ApplicationId

INNER JOIN Pub_AidBasicInformation ON Pub_Application.AidBasicInfoId = Pub_AidBasicInformation.AidBasicInfoId                                                           

INNER  JOIN(select ConsultId,dbo.f_GetClinical(ConsultId) as Clinical

            from Pub_Consult1) Clinicaltb on Clinicaltb.ConsultId=Pub_Consult1.ConsultId

left join (select distinct ApplicationId, sum(TraniningNumber) as num from dbo.Review_Aid_UseTraining_Record

           where  AidReferralId is null 

           group by  ApplicationId) tab

           on tab.ApplicationId=Pub_Consult1.ApplicationId

left JOIN cte3 on cte3.ConsultId=Pub_Consult1.ConsultId                              

LEFT OUTER JOIN Pub_User ON Pub_Application.ReviewUserId = Pub_User.UserId

           where Pub_Consult1.Directory = 0

order by Pub_Application.CreateOn desc
           </textarea>
          </div>
          <div class="crayon-main" style="">
           <table class="crayon-table">
            <tbody>
             <tr class="crayon-row">
              <td class="crayon-nums " data-settings="show">
               <div class="crayon-nums-content" style="font-size: 12px !important; line-height: 15px !important;">
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-1">
                 1
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-2">
                 2
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-3">
                 3
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-4">
                 4
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-5">
                 5
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-6">
                 6
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-7">
                 7
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-8">
                 8
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-9">
                 9
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-10">
                 10
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-11">
                 11
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-12">
                 12
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-13">
                 13
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-14">
                 14
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-15">
                 15
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-16">
                 16
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-17">
                 17
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-18">
                 18
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-19">
                 19
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-20">
                 20
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-21">
                 21
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-22">
                 22
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-23">
                 23
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-24">
                 24
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-25">
                 25
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-26">
                 26
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-27">
                 27
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-28">
                 28
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-29">
                 29
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-30">
                 30
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-31">
                 31
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-32">
                 32
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-33">
                 33
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-34">
                 34
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-35">
                 35
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-36">
                 36
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-37">
                 37
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-38">
                 38
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-39">
                 39
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-40">
                 40
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-41">
                 41
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-42">
                 42
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-43">
                 43
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-44">
                 44
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-45">
                 45
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-46">
                 46
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-47">
                 47
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-48">
                 48
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-49">
                 49
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-50">
                 50
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-51">
                 51
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-52">
                 52
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-53">
                 53
                </div>
                <div class="crayon-num crayon-striped-num" data-line="crayon-5768536d95af8723694143-54">
                 54
                </div>
                <div class="crayon-num" data-line="crayon-5768536d95af8723694143-55">
                 55
                </div>
               </div>
              </td>
              <td class="crayon-code">
               <div class="crayon-pre" style="font-size: 12px !important; line-height: 15px !important; -moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4;">
                <div class="crayon-line" id="crayon-5768536d95af8723694143-1">
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-e">
                  distinct
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  AidBasicInfoId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-2">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-3">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  UserName
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-4">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-5">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  District
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-6">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-7">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  Street
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-8">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-9">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  Community
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-10">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-11">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  DisCard
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-12">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-13">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  CreateOn
                 </span>
                 <span class="crayon-st">
                  AS
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  AppCreateOn
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-14">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-15">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_User
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  UserName
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  DepartmentUserName
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-h">
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-16">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-17">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-18">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-19">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  CaseId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-20">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-21">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Clinicaltb
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  Clinical
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-22">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-23">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  cte3
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  AidName
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-24">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-25">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  IsUseTraining
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-26">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-27">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-28">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-29">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  tab
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  num
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-30">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-31">
                 <span class="crayon-e">
                  from
                 </span>
                 <span class="crayon-e">
                  Pub_Consult1
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-32">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-33">
                 <span class="crayon-e">
                  INNER
                 </span>
                 <span class="crayon-e">
                  JOIN
                 </span>
                 <span class="crayon-e">
                  Pub_Application
                 </span>
                 <span class="crayon-e">
                  ON
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  ApplicationId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-34">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-35">
                 <span class="crayon-e">
                  INNER
                 </span>
                 <span class="crayon-e">
                  JOIN
                 </span>
                 <span class="crayon-e">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-e">
                  ON
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  AidBasicInfoId
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_AidBasicInformation
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  AidBasicInfoId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-36">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-37">
                 <span class="crayon-e">
                  INNER
                 </span>
                 <span class="crayon-e">
                  JOIN
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  f_GetClinical
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  Clinical
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-38">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-39">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  from
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  Clinicaltb
                 </span>
                 <span class="crayon-e">
                  on
                 </span>
                 <span class="crayon-v">
                  Clinicaltb
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  ConsultId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-40">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-41">
                 <span class="crayon-e">
                  left
                 </span>
                 <span class="crayon-e">
                  join
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-e">
                  select
                 </span>
                 <span class="crayon-e">
                  distinct
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-sy">
                  ,
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  sum
                 </span>
                 <span class="crayon-sy">
                  (
                 </span>
                 <span class="crayon-v">
                  TraniningNumber
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-st">
                  as
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  num
                 </span>
                 <span class="crayon-e">
                  from
                 </span>
                 <span class="crayon-v">
                  dbo
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  Review_Aid_UseTraining_Record
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-42">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-43">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  where
                 </span>
                 <span class="crayon-e">
                  AidReferralId
                 </span>
                 <span class="crayon-st">
                  is
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-t">
                  null
                 </span>
                 <span class="crayon-h">
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-44">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-45">
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  group
                 </span>
                 <span class="crayon-e">
                  by
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-sy">
                  )
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-e">
                  tab
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-46">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-47">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  on
                 </span>
                 <span class="crayon-v">
                  tab
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ApplicationId
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  ApplicationId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-48">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-49">
                 <span class="crayon-e">
                  left
                 </span>
                 <span class="crayon-e">
                  JOIN
                 </span>
                 <span class="crayon-e">
                  cte3
                 </span>
                 <span class="crayon-e">
                  on
                 </span>
                 <span class="crayon-v">
                  cte3
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ConsultId
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  ConsultId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-50">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-51">
                 <span class="crayon-e">
                  LEFT
                 </span>
                 <span class="crayon-e">
                  OUTER
                 </span>
                 <span class="crayon-e">
                  JOIN
                 </span>
                 <span class="crayon-e">
                  Pub_User
                 </span>
                 <span class="crayon-e">
                  ON
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  ReviewUserId
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-v">
                  Pub_User
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  UserId
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-52">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-53">
                 <span class="crayon-e">
                 </span>
                 <span class="crayon-e">
                  where
                 </span>
                 <span class="crayon-v">
                  Pub_Consult1
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-v">
                  Directory
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-o">
                  =
                 </span>
                 <span class="crayon-h">
                 </span>
                 <span class="crayon-cn">
                  0
                 </span>
                </div>
                <div class="crayon-line crayon-striped-line" id="crayon-5768536d95af8723694143-54">
                </div>
                <div class="crayon-line" id="crayon-5768536d95af8723694143-55">
                 <span class="crayon-e">
                  order
                 </span>
                 <span class="crayon-e">
                  by
                 </span>
                 <span class="crayon-v">
                  Pub_Application
                 </span>
                 <span class="crayon-sy">
                  .
                 </span>
                 <span class="crayon-e">
                  CreateOn
                 </span>
                 <span class="crayon-v">
                  desc
                 </span>
                </div>
               </div>
              </td>
             </tr>
            </tbody>
           </table>
          </div>
         </div>
         <!-- [Format Time: 0.0415 seconds] -->
         <p>
         </p>
         <div class="cnblogs_code_toolbar">
          <span class="cnblogs_code_copy">
           <p title="复制代码">
            <img src="http://common.cnblogs.com/images/copycode.gif"/>
           </p>
          </span>
         </div>
        </div>
       </div>
       <p>
       </p>
       <p align="left">
        这样基本上就完事了，在没有建立索引的情况下需要8秒钟，比没索引用函数还是快了27秒。
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151552388139099.jpg"/>
       </p>
       <p align="left">
        把索引放进去，就只需1.6秒了，比建立索引用函数而不用子查询和sql for xml path快了1.9秒
       </p>
       <p align="left">
       </p>
       <p align="left">
        <img src="http://images0.cnblogs.com/blog2015/619675/201507/151552524077746.jpg"/>
       </p>
       <p align="left">
        查询里面还有个地方用了函数，估计再优化下还能提高执行效率，因为时间有限再加上篇幅有点长了，在这里就不多讲了。
       </p>
       <p align="left">
        最后做个总结吧，查询优化不外乎以下这几种办法：
       </p>
       <p align="left">
        1：增加索引或重建索引。通常在外键，连接字段，排序字段，过滤查询的字段建立索引，也可通过数据库引擎优化顾问提供的信息去建索引。有时候当你创建索引时，会发现查询还是按照索引扫描或聚集索引扫描的方式去执行，而没有去索引查找，这时很可能是你的查询字段和where条件字段没有全部包含在索引字段当中，解决这个问题的办法就是多建立索引，或者在创建索引时Include相应的字段，让索引字段覆盖你的查询字段和where条件字段。
       </p>
       <p align="left">
        2：调整查询语句，前提要先看懂别人的查询，搞清楚业务逻辑。
       </p>
       <p align="left">
        3：表分区，大数据量可以考虑。
       </p>
       <p align="left">
        4：提高服务器硬件配置。
       </p>
      </div>
      <div>
       <strong>
        注：转载文章均来自于公开网络，仅供学习使用，不会用于任何商业用途，如果侵犯到原作者的权益，请您与我们联系删除或者授权事宜，联系邮箱：contact@dataunion.org。转载数盟网站文章请注明原文章作者，否则产生的任何版权纠纷与数盟无关。
       </strong>
      </div>
      <!--content_text-->
      <div class="fenxian">
       <!-- JiaThis Button BEGIN -->
       <div class="jiathis_style_32x32">
        <p class="jiathis_button_weixin">
        </p>
        <p class="jiathis_button_tsina">
        </p>
        <p class="jiathis_button_qzone">
        </p>
        <p class="jiathis_button_cqq">
        </p>
        <p class="jiathis_button_tumblr">
        </p>
        <a class="jiathis jiathis_txt jtico jtico_jiathis" href="http://www.jiathis.com/share" target="_blank">
        </a>
        <p class="jiathis_counter_style">
        </p>
       </div>
       <!-- JiaThis Button END -->
      </div>
     </article>
     <!--content-->
     <!--相关文章-->
     <div class="xianguan">
      <div class="xianguantitle">
       相关文章！
      </div>
      <ul class="pic">
       <li>
        <a href="http://dataunion.org/20824.html">
         <img src="http://dataunion.org/wp-content/uploads/2015/09/t018630756a7e263b33-300x165.jpg"/>
        </a>
        <a class="link" href="http://dataunion.org/20824.html" rel="bookmark" title="如何判断一笔交易是否属于欺诈？你只是需要一点数据挖掘">
         如何判断一笔交易是否属于欺诈？你只是需要一点数据挖掘
        </a>
       </li>
       <li>
        <a href="http://dataunion.org/20820.html">
         <img src="http://dataunion.org/wp-content/uploads/2015/09/1-300x200.jpg"/>
        </a>
        <a class="link" href="http://dataunion.org/20820.html" rel="bookmark" title="人们对Python在企业级开发中的10大误解">
         人们对Python在企业级开发中的10大误解
        </a>
       </li>
       <li>
        <a href="http://dataunion.org/20811.html">
         <img src="http://dataunion.org/wp-content/uploads/2015/09/t0133fcacae8523307b_副本-300x200.jpg"/>
        </a>
        <a class="link" href="http://dataunion.org/20811.html" rel="bookmark" title="大神亲传：26条深度学习的金科玉律！">
         大神亲传：26条深度学习的金科玉律！
        </a>
       </li>
       <li>
        <a href="http://dataunion.org/20808.html">
         <img src="http://dataunion.org/wp-content/uploads/2015/09/640.webp-11-300x137.jpg"/>
        </a>
        <a class="link" href="http://dataunion.org/20808.html" rel="bookmark" title="我们是如何在一张地图上表现86万个数据的">
         我们是如何在一张地图上表现86万个数据的
        </a>
       </li>
      </ul>
     </div>
     <!--相关文章-->
     <div class="comment" id="comments">
      <!-- You can start editing here. -->
      <!-- If comments are open, but there are no comments. -->
      <div class="title">
       期待你一针见血的评论，Come on！
      </div>
      <div id="respond">
       <p>
        不用想啦，马上
        <a href="http://dataunion.org/wp-login.php?redirect_to=http%3A%2F%2Fdataunion.org%2F20037.html">
         "登录"
        </a>
        发表自已的想法.
       </p>
      </div>
     </div>
     <!-- .nav-single -->
    </div>
    <!--Container End-->
    <aside id="sitebar">
     <div class="sitebar_list2">
      <div class="wptag">
       <span class="tagtitle">
        热门标签+
       </span>
       <div class="tagg">
        <ul class="menu" id="menu-%e5%8f%8b%e6%83%85%e9%93%be%e6%8e%a5">
         <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-1605" id="menu-item-1605">
          <a href="http://taidizh.com/">
           泰迪智慧
          </a>
         </li>
         <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-20884" id="menu-item-20884">
          <a href="http://www.transwarp.cn/">
           星环科技
          </a>
         </li>
         <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-3538" id="menu-item-3538">
          <a href="http://datall.org/">
           珈和遥感
          </a>
         </li>
         <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-20888" id="menu-item-20888">
          <a href="http://www.chinahadoop.cn/">
           小象学院
          </a>
         </li>
        </ul>
       </div>
      </div>
     </div>
     <div class="sitebar_list">
      <div class="textwidget">
       <div align="center">
        <a href="http://study.163.com/course/courseMain.htm?courseId=991022" target="_blank">
         <img src="http://dataunion.org/wp-content/uploads/2016/03/dv.jpg"/>
        </a>
       </div>
      </div>
     </div>
     <div class="sitebar_list">
      <h4 class="sitebar_title">
       文章分类
      </h4>
      <div class="tagcloud">
       <a class="tag-link-44" href="http://dataunion.org/category/industry/demo" style="font-size: 10.204724409449pt;" title="4个话题">
        Demo展示
       </a>
       <a class="tag-link-31" href="http://dataunion.org/category/experts" style="font-size: 15.826771653543pt;" title="52个话题">
        专家团队
       </a>
       <a class="tag-link-870" href="http://dataunion.org/category/tech/ai" style="font-size: 19.795275590551pt;" title="273个话题">
        人工智能
       </a>
       <a class="tag-link-488" href="http://dataunion.org/category/%e5%8a%a0%e5%85%a5%e6%95%b0%e7%9b%9f" style="font-size: 8pt;" title="1个话题">
        加入数盟
       </a>
       <a class="tag-link-869" href="http://dataunion.org/category/tech/viz" style="font-size: 17.204724409449pt;" title="93个话题">
        可视化
       </a>
       <a class="tag-link-30" href="http://dataunion.org/category/partners" style="font-size: 10.645669291339pt;" title="5个话题">
        合作伙伴
       </a>
       <a class="tag-link-889" href="http://dataunion.org/category/parterc" style="font-size: 11.582677165354pt;" title="8个话题">
        合作会议
       </a>
       <a class="tag-link-104" href="http://dataunion.org/category/books" style="font-size: 12.96062992126pt;" title="15个话题">
        图书
       </a>
       <a class="tag-link-220" href="http://dataunion.org/category/tech/base" style="font-size: 19.850393700787pt;" title="281个话题">
        基础架构
       </a>
       <a class="tag-link-219" href="http://dataunion.org/category/tech/analysis" style="font-size: 19.409448818898pt;" title="232个话题">
        数据分析
       </a>
       <a class="tag-link-887" href="http://dataunion.org/category/tech/dm" style="font-size: 13.291338582677pt;" title="17个话题">
        数据挖掘
       </a>
       <a class="tag-link-34" href="http://dataunion.org/category/tech" style="font-size: 20.732283464567pt;" title="404个话题">
        文章
       </a>
       <a class="tag-link-1" href="http://dataunion.org/category/uncategorized" style="font-size: 22pt;" title="693个话题">
        未分类
       </a>
       <a class="tag-link-4" href="http://dataunion.org/category/events" style="font-size: 14.503937007874pt;" title="29个话题">
        活动
       </a>
       <a class="tag-link-890" href="http://dataunion.org/category/tech/%e6%b7%b1%e5%ba%a6%e5%ad%a6%e4%b9%a0" style="font-size: 10.204724409449pt;" title="4个话题">
        深度学习
       </a>
       <a class="tag-link-221" href="http://dataunion.org/category/tech/devl" style="font-size: 18.968503937008pt;" title="193个话题">
        编程语言
       </a>
       <a class="tag-link-888" href="http://dataunion.org/category/career" style="font-size: 15.661417322835pt;" title="48个话题">
        职业规划
       </a>
       <a class="tag-link-5" href="http://dataunion.org/category/jobs" style="font-size: 14.11811023622pt;" title="25个话题">
        职位
       </a>
       <a class="tag-link-871" href="http://dataunion.org/category/industry" style="font-size: 15.716535433071pt;" title="49个话题">
        行业
       </a>
       <a class="tag-link-613" href="http://dataunion.org/category/industry/case" style="font-size: 16.984251968504pt;" title="84个话题">
        行业应用
       </a>
       <a class="tag-link-885" href="http://dataunion.org/category/industry/news" style="font-size: 17.425196850394pt;" title="102个话题">
        行业资讯
       </a>
       <a class="tag-link-10" href="http://dataunion.org/category/training" style="font-size: 14.228346456693pt;" title="26个话题">
        课程
       </a>
       <a class="tag-link-16" href="http://dataunion.org/category/sources" style="font-size: 15.661417322835pt;" title="48个话题">
        资源
       </a>
      </div>
     </div>
     <div class="sitebar_list">
      <h4 class="sitebar_title">
       功能
      </h4>
      <ul>
       <li>
        <a href="http://dataunion.org/wp-login.php?action=register">
         注册
        </a>
       </li>
       <li>
        <a href="http://dataunion.org/wp-login.php">
         登录
        </a>
       </li>
       <li>
        <a href="http://dataunion.org/feed">
         文章
         <abbr title="Really Simple Syndication">
          RSS
         </abbr>
        </a>
       </li>
       <li>
        <a href="http://dataunion.org/comments/feed">
         评论
         <abbr title="Really Simple Syndication">
          RSS
         </abbr>
        </a>
       </li>
       <li>
        <a href="https://cn.wordpress.org/" title="基于WordPress，一个优美、先进的个人信息发布平台。">
         WordPress.org
        </a>
       </li>
      </ul>
     </div>
    </aside>
    <div class="clear">
    </div>
   </div>
   <!--main-->
   ﻿
   <footer id="dibu">
    <div class="about">
     <div class="right">
      <ul class="menu" id="menu-%e5%ba%95%e9%83%a8%e8%8f%9c%e5%8d%95">
       <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-18024" id="menu-item-18024">
        <a href="http://dataunion.org/category/partners">
         合作伙伴
        </a>
       </li>
       <li class="menu-item menu-item-type-post_type menu-item-object-page menu-item-20881" id="menu-item-20881">
        <a href="http://dataunion.org/contribute">
         文章投稿
        </a>
       </li>
       <li class="menu-item menu-item-type-taxonomy menu-item-object-category menu-item-20872" id="menu-item-20872">
        <a href="http://dataunion.org/category/%e5%8a%a0%e5%85%a5%e6%95%b0%e7%9b%9f">
         加入数盟
        </a>
       </li>
       <li class="menu-item menu-item-type-post_type menu-item-object-page menu-item-22441" id="menu-item-22441">
        <a href="http://dataunion.org/f-links">
         友情链接
        </a>
       </li>
       <li class="menu-item menu-item-type-post_type menu-item-object-page menu-item-20874" id="menu-item-20874">
        <a href="http://dataunion.org/aboutus">
         关于数盟
        </a>
       </li>
      </ul>
      <p class="banquan">
       数盟社区        ，
        做最棒的数据科学社区
      </p>
     </div>
     <div class="left">
      <ul class="bottomlist">
       <li>
        <a href="http://weibo.com/DataScientistUnion  " target="_blank" 　title="">
         <img src="http://dataunion.org/wp-content/themes/yzipi/images/weibo.png"/>
        </a>
       </li>
       <li>
        <a class="cd-popup-trigger" href="http://dataunion.org/20037.html#0">
         <img src="http://dataunion.org/wp-content/themes/yzipi/images/weixin.png"/>
        </a>
       </li>
      </ul>
      <div class="cd-popup">
       <div class="cd-popup-container">
        <h1>
         扫描二维码,加微信公众号
        </h1>
        <img src="http://dataunion.org/wp-content/themes/yzipi/images/2014-12-06-1515289049.png"/>
        <a class="cd-popup-close" href="http://dataunion.org/20037.html">
        </a>
       </div>
       <!-- cd-popup-container -->
      </div>
      <!-- cd-popup -->
     </div>
    </div>
    <!--about-->
    <div class="bottom">
     <a href="http://dataunion.org/">
      数盟社区
     </a>
     <a href="http://www.miitbeian.gov.cn/" rel="external nofollow" target="_blank">
      京ICP备14026740号
     </a>
     联系我们：
     <a href="mailto:contact@dataunion.org" target="_blank">
      contact@dataunion.org
     </a>
     <div class="tongji">
     </div>
     <!--bottom-->
     <div class="scroll" id="scroll" style="display:none;">
      ︿
     </div>
    </div>
   </footer>
   <!--dibu-->
  </div>
 </body>
</html>